/**
 * Copyright ©2003~2019年2月12日 szkingdom
 * @author raohj
 * @date 2019年2月12日
 * @file RJdbc.java
 * @comment : please input your comment to this file
 * @version : input version
 * @modify:
 * 		1、 2019年2月12日 由 raohj 新建文件
 */
package com.ccnu.database;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;
import java.util.Random;

/**
 * @author raohj
 * @date 2019年2月12日
 * @version
 * @comment to add your comment
 */
public class RJDBC {
	private static final Logger log = LoggerFactory.getLogger(RJDBC.class);
	
	private static List<String> custNoList = new ArrayList<String>();

	public static void test() {
		// 测试Oracle数据库的连接
		JDBCOracle();
	}

	public static void JDBCOracle() {
		String driver = "oracle.jdbc.driver.OracleDriver";
		String url = "jdbc:oracle:thin:@127.0.0.1:1522:orcl";
		String user = "RHJ";
		String password = "RHJ";
		try {
			// 加载Oracle驱动
			Class.forName(driver);

			// 获取数据库连接
			Connection conn = DriverManager.getConnection(url, user, password);

			// 设置为非自动提交
			conn.setAutoCommit(false);
			
			log.info("================测试MetaData===============");
			metaData(conn);
			
			// 创建会话
			Statement stat = conn.createStatement();

			// 执行查询语句
			ResultSet result = stat.executeQuery("select * from T_CUST");
			int i = 0;
			while (result.next()) {
				String custNo = result.getString(1);
				String custName = result.getString("CUST_NAME");
				log.info("客户号[" + custNo + "]，客户名称[" + custName + "]");
				if (i++ > 10) {
					break;
				}
			}
			result.close();

			// 执行更新操作
			int nupdCnt = stat
					.executeUpdate("update T_CUST set cust_name='TEST_CUST' where cust_no='2018103000000204'");
			log.info("执行更新语句，影响的记录数为[" + nupdCnt + "]");

			// 执行查询语句
			stat.execute("select * from T_CUST where cust_no='2018103000000204'");
			result = stat.getResultSet();
			if (result != null) {
				// 查找某一名称指定列的列序号
				int idtypeCol = result.findColumn("ID_TYPE");
				int idcodeCol = result.findColumn("ID_CODE");
				log.info("ID_TYPE col[" + idtypeCol + "], ID_CODE col[" + idcodeCol + "]");
				while (result.next()) {
					String custNo = result.getString(1);
					String custName = result.getString("CUST_NAME");
					log.info("客户号[" + custNo + "]，客户名称[" + custName + "]");
					if (i++ > 10) {
						break;
					}
				}
				result.close();
			}
			conn.rollback();

			// 执行查询语句
			log.info("执行回滚语句以后，查看数据");
			queryCheck(conn);

			// 测试删除语句
			int nDelCnt = stat.executeUpdate("delete from T_CUST where cust_no='2018103000000204'");
			log.info("执行删除语句，影响的记录数为[" + nDelCnt + "]");
			conn.commit();
			log.info("执行事务提交以后，再次查询数据库中数据");
			stat.execute("select * from T_CUST where cust_no='2018103000000204'");
			result = stat.getResultSet();
			if (result != null && result.next()) {
				log.error("查询有数据，异常");
				result.close();
			} else {
				log.info("查询无数据，正常");
			}
			
			// 测试插入语句
			log.info("================测试插入操作===============");
			int nInsertCnt = stat.executeUpdate(
					"insert into t_cust (CUST_NO, CUST_TYPE, CUST_NAME, ID_TYPE, ID_CODE, BANK_ACCT) values ('2018103000000204', 'INVE', 'cust000204', '0', '123456789012300204', '62290100000000100204')");
			log.info("执行插入语句，影响的记录数为[" + nInsertCnt + "]");
			conn.commit();
			log.info("执行事务提交以后，再次查询数据库中数据");
			stat.execute("select * from T_CUST where cust_no='2018103000000204'");
			result = stat.getResultSet();
			if (result != null && result.next()) {
				log.info("执行完插入，可以查出结果，正常");
				result.close();
			} else {
				log.error("执行完插入，查询不出结果，异常");
			}
			stat.close();

			log.info("================测试可更新的结果集===============");
			updateResult(conn);
			
			log.info("================测试可插入的结果集===============");
			insertResult(conn);
			
			log.info("================测试保存点===============");
			savepoint(conn);
			
			log.info("================测试批量操作===============");
			batchOpr(conn);

			conn.close();

		} catch (SQLException | ClassNotFoundException e) {
			e.printStackTrace();
		}

		log.info("测试结束");
	}
	
	private static void metaData(Connection conn) throws SQLException {
		// 执行查询语句
		Statement stat = conn.createStatement();
		
		String schema = "RHJ";
		
		log.info("================测试ResultSetMetaData===============");
		ResultSet result = stat.executeQuery("select * from T_CUST where 1=0");
		ResultSetMetaData metaData = result.getMetaData();
		StringBuilder col = new StringBuilder();
		for (int i = 0; i < metaData.getColumnCount(); i++) {
			col.append(metaData.getColumnName(i + 1)).append("[").append(metaData.getColumnTypeName(i + 1))
					.append("]|");
		}
		log.info("显示表结构tablename[" + metaData.getTableName(1) + "], cols{" + col + "}");
		
		log.info("================测试DatabaseMetaData===============");
		DatabaseMetaData dbMetaData = conn.getMetaData();
		log.info("JDBC版本号[" + dbMetaData.getJDBCMajorVersion() + "], 小版本号[" + dbMetaData.getJDBCMinorVersion() + "]"
				+ "最大连接数[" + dbMetaData.getMaxConnections() + "]"
				+ "最大句柄数[" + dbMetaData.getMaxStatements() + "]");
		
		ResultSet tables = dbMetaData.getTables(null, schema, null, new String[] {"TABLE"});
		while(tables.next()) {
			log.info("表[" + tables.getString(3) + "]");
		}
	}

	private static void queryCheck(Connection conn) throws SQLException {
		Statement stat = conn.createStatement();
		stat.execute("select * from T_CUST where cust_no='2018103000000204'");
		ResultSet result = stat.getResultSet();
		if (result != null) {
			int i = 0;
			while (result.next()) {
				String custNo = result.getString(1);
				String custName = result.getString("CUST_NAME");
				log.info("客户号[" + custNo + "]，客户名称[" + custName + "]");
				if (i++ > 10) {
					break;
				}
			}
			result.close();
		}
		stat.close();
	}
	
	private static void queryCheck(Connection conn, String custNo) throws SQLException {
		Statement stat = conn.createStatement();
		stat.execute("select * from T_CUST where cust_no='" + custNo + "'");
		ResultSet result = stat.getResultSet();
		if (result != null) {
			int i = 0;
			while (result.next()) {
				String cust_No = result.getString(1);
				String cust_Name = result.getString("CUST_NAME");
				log.info("客户号[" + cust_No + "]，客户名称[" + cust_Name + "]");
				if (i++ > 10) {
					break;
				}
			}
			result.close();
		}
		stat.close();
	}
	
	private static void queryCheckByCustNos(Connection conn) throws SQLException {
		StringBuilder custNos = new StringBuilder();
		for(String custNo : custNoList) {
			custNos.append("'").append(custNo).append("'").append(",");
		}
		custNos.deleteCharAt(custNos.length()-1);
		
		Statement stat = conn.createStatement();
		stat.execute("select * from T_CUST where cust_no in (" + custNos + ")");
		ResultSet result = stat.getResultSet();
		if (result != null) {
			int i = 0;
			while (result.next()) {
				String cust_No = result.getString(1);
				String cust_Name = result.getString("CUST_NAME");
				log.info("客户号[" + cust_No + "]，客户名称[" + cust_Name + "]");
				if (i++ > 10) {
					break;
				}
			}
			result.close();
		}
		stat.close();
	}
	
	/**
	 * @author raohj
	 * @date 2019年2月13日
	 * @comment 测试可更新的结果集
	 * @param 
	 * @return 
	 * @param conn
	 * @throws SQLException
	 */
	private static void updateResult(Connection conn) throws SQLException {
		// 用select * 会导致该ResultSet强制readonly，但是同样的效果语句如果写成select 字段字段1，字段2，字段3...就会正常完成。
		Statement updateStat = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE);
		ResultSet resultUpdate = updateStat.executeQuery("select CUST_NO,CUST_NAME from T_CUST where cust_no='2018103000000204'");
		while (resultUpdate.next()) {
			// 更新某个字段
			resultUpdate.updateString("CUST_NAME", "CUSTNAME204");
			// 更新到数据库
			resultUpdate.updateRow();
		}
		conn.commit();
		updateStat.close();
		log.info("执行更新语句以后，查看数据");
		queryCheck(conn);
	}
	
	/**
	 * @author raohj
	 * @date 2019年2月13日
	 * @comment 测试插入结果集
	 * @param 
	 * @return 
	 * @param conn
	 * @throws SQLException
	 */
	private static void insertResult(Connection conn) throws SQLException {
		// 用select * 会导致该ResultSet强制readonly，但是同样的效果语句如果写成select 字段字段1，字段2，字段3...就会正常完成。
		int index = new Random().nextInt(10000);
		Statement updateStat = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE);
		ResultSet resultUpdate = updateStat.executeQuery("select CUST_NO, CUST_TYPE, CUST_NAME, ID_TYPE, ID_CODE, BANK_ACCT from T_CUST where cust_no='2018103000000204'");
		resultUpdate.moveToInsertRow();
		resultUpdate.updateString("CUST_NO", "CUST_NO"+index);
		resultUpdate.updateString("CUST_TYPE", "INVE");
		resultUpdate.updateString("CUST_NAME", "CUST_NAME"+index);
		resultUpdate.updateString("ID_TYPE", "0");
		resultUpdate.updateString("ID_CODE", "ID_CODE"+index);
		resultUpdate.updateString("BANK_ACCT", "BANK_ACCT"+index);
		resultUpdate.insertRow();
		conn.commit();
		updateStat.close();
		log.info("执行更新语句以后，查看数据");
		queryCheck(conn, "CUST_NO"+index);
	}
	
	/**
	 * @author raohj
	 * @date 2019年2月13日
	 * @comment 测试保存点
	 * @param 
	 * @return 
	 * @param conn
	 * @throws SQLException 
	 */
	private static void savepoint(Connection conn) throws SQLException {
		boolean oldCommitFlag = conn.getAutoCommit();
		// 设置为非自动提交
		conn.setAutoCommit(false);
		
		Statement updateStat = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE);
		Savepoint svp1 = null;
		Savepoint svp2 = null;
		for(int i=0; i<10; i++) {
			int index = new Random().nextInt(10000);
			ResultSet resultUpdate = updateStat.executeQuery("select CUST_NO, CUST_TYPE, CUST_NAME, ID_TYPE, ID_CODE, BANK_ACCT from T_CUST where cust_no='2018103000000204'");
			resultUpdate.moveToInsertRow();
			resultUpdate.updateString("CUST_NO", "CUST_NOA"+index);
			resultUpdate.updateString("CUST_TYPE", "INVE");
			resultUpdate.updateString("CUST_NAME", "CUST_NAME"+index);
			resultUpdate.updateString("ID_TYPE", "0");
			resultUpdate.updateString("ID_CODE", "ID_CODE"+index);
			resultUpdate.updateString("BANK_ACCT", "BANK_ACCT"+index);
			resultUpdate.insertRow();
			if(i==5) {
				// 设置恢复点
				svp1 = conn.setSavepoint();
			}
			
			if(i==7) {
				svp2 = conn.setSavepoint();
			}
			custNoList.add("CUST_NOA"+index);
		}
		// 释放某个保存点，此特性Oracle不支持
//		conn.releaseSavepoint(svp2);
		// 回滚到某个保存点
		conn.rollback(svp1);
		conn.commit();
		// 恢复自动提交标志
		conn.setAutoCommit(oldCommitFlag);
		// 测试提交结果数据
		queryCheckByCustNos(conn);
	}
	
	/**
	 * @author raohj
	 * @date 2019年2月13日
	 * @comment 执行批量操作
	 * @param 
	 * @return 
	 * @param conn
	 * @throws SQLException 
	 */
	private static void batchOpr(Connection conn) throws SQLException {
		boolean oldCommitFlag = conn.getAutoCommit();
		// 设置为非自动提交
		conn.setAutoCommit(false);
		Statement stat = conn.createStatement();
		for(String custNo : custNoList) {
			String delSql = "delete from T_CUST where CUST_NO ='" + custNo+"'";
			stat.addBatch(delSql);
		}
		int[] resultCnt = stat.executeBatch();
		for (int i = 0; i < resultCnt.length; i++) {
			log.info("第" + i +"条命令执行的结果为" + resultCnt[i]);
		}
		conn.commit();
		
		conn.setAutoCommit(oldCommitFlag);
	}
}
